Namespace persistent

    Public Class Bulk_Service_Requests_Secim
        Public Shared Function GridDataGetir(ByVal p_custcode As String, ByVal p_sme_la_flag As String, ByVal p_seviye As String) As DataTable
            Dim l_dt As DataTable
            Dim l_SME_LA As String
            Dim sql As String
            Dim l_HighestCustcode As String
            Dim l_HighestCustomerid As String

            sql = "select  toplevel.CUSTCODE, toplevel.CUSTOMER_ID, decode(decode(nvl(curlevel.CSLEVEL,0), [TAG2], curlevel.CUSTOMER_ID, nvl(curlevel.CUSTOMER_ID_HIGH, 0)), 0, 'S', 'L') SME_LA_FLAG " + vbCrLf
            sql = sql + "	from CUSTOMER_ALL curlevel, CUSTOMER_ALL toplevel  " + vbCrLf
            sql = sql + "	where curlevel.CUSTCODE = '[TAG1]' " + vbCrLf
            sql = sql + "		and decode(decode(nvl(curlevel.CSLEVEL,0), [TAG2], curlevel.CUSTOMER_ID, nvl(curlevel.CUSTOMER_ID_HIGH, 0)), " + vbCrLf
            sql = sql + "				0, (select CUSTOMER_ID from CONTRACT_ALL where CO_ID = (select max(a.CO_ID) from CONTRACT_ALL a, CURR_CO_STATUS b where a.CUSTOMER_ID = curlevel.CUSTOMER_ID and a.CO_ID = b.CO_ID and b.CH_STATUS <> 'd')), " + vbCrLf
            sql = sql + "				sysadm.fn_la_getcustidhigh([TAG2], curlevel.CUSTOMER_ID)) = toplevel.CUSTOMER_ID " + vbCrLf
            sql = sql.Replace("[TAG1]", p_custcode)
            sql = sql.Replace("[TAG2]", p_seviye)
            Dim l_dt2 As DataTable
            l_dt2 = myDBConn.myORATable(sql)
            l_HighestCustcode = CStr(l_dt2.Rows(0).Item("CUSTCODE"))
            l_SME_LA = CStr(l_dt2.Rows(0).Item("SME_LA_FLAG"))
            l_HighestCustomerid = CStr(l_dt2.Rows(0).Item("CUSTOMER_ID"))

            If l_SME_LA = "L" Then
                sql = "select  'N' CHOOSE, b.CO_ID, sysadm.fn_coid_to_msisdn(b.CO_ID), decode(c.CH_STATUS, 'a', 'Aktif', 's', 'Suspend', 'd', 'Deaktif', 'o', 'Onhold') Status " + vbCrLf
                sql = sql + "   from CUSTOMER_ALL a, CONTRACT_ALL b, CURR_CO_STATUS c " + vbCrLf
                sql = sql + "   where (a.CUSTCODE = '[TAG1]' or a.CUSTCODE like '[TAG1].%') " + vbCrLf
                sql = sql + "       and a.CUSTOMER_ID = b.CUSTOMER_ID " + vbCrLf
                sql = sql + "		and b.CO_ID = c.CO_ID " + vbCrLf
                sql = sql + " order by b.CO_ID"
                'sql = sql + "		and c.CH_STATUS = 'a' " + vbCrLf

                sql = sql.Replace("[TAG1]", l_HighestCustcode)
                l_dt = myDBConn.myORATable(sql)
            ElseIf l_SME_LA = "S" Then
                sql = "select  'N' CHOOSE, a.CO_ID, sysadm.fn_coid_to_msisdn(a.CO_ID), decode(b.CH_STATUS, 'a', 'Aktif', 's', 'Suspend', 'd', 'Deaktif', 'o', 'Onhold') Status " + vbCrLf
                sql = sql + "	from CONTRACT_ALL a, CURR_CO_STATUS b " + vbCrLf
                sql = sql + "	where a.CUSTOMER_ID = [TAG1] " + vbCrLf
                sql = sql + "		and a.CO_ID = b.CO_ID " + vbCrLf
                sql = sql + " order by b.CO_ID"
                'sql = sql + "		and b.CH_STATUS = 'a' " + vbCrLf
                sql = sql.Replace("[TAG1]", l_HighestCustomerid)
                l_dt = myDBConn.myORATable(sql)
            End If
            Return l_dt
        End Function


    End Class

End Namespace
